×
☰ See All Chapters

JPA Query - Java Persistence Query Language (JPQL)

The javax.persistence.Query interface is used to issue queries in JPA. Java Persistence Query Language (JPQL) is the query language used in JPA. The syntax of JPQL is almost same as SQL, but it is object-oriented rather than table-oriented. The EntityManager.createQuery  method is used to create a Query instance from a given JPQL string. getResultList() method of Query object is used to execute the query. This method returns a List containing the matching entity class objects.

EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU");

EntityManager em = emf.createEntityManager();

Query q = em.createQuery("SELECT x FROM Student x");

List<Student> results = (List<Student>) q.getResultList();

JPQL is object-oriented rather than table-oriented. When we construct String JPQL as “SELECT x FROM Student x” The string Student is the name of the entity class, not the table name as in SQL. So “SELECT x FROM STUDENT x” is wrong unless if there are any entity class with the name STUDENT. Arbitrary identifiers can be assigned to entities in JPQL in From Clause, so that they can be referenced elsewhere in the query. In the query example above, the identifier x is assigned to the entity Student. The as keyword can optionally be used when declaring identifiers in the from clause. “SELECT x FROM Student x” and “SELECT x FROM Student AS x” are synonymous.  Keywords in JPQL expressions are case-insensitive, but entity, identifier, and member names are not. For example:

SELECT x FROM Student x WHERE x.sname = ‘Manu Manjunatha’ => Correct

Select x From Student x Where x.sname = ‘Manu Manjunatha’ => Correct

Select x From STUDENT x Where x.sName = ‘Manu Manjunatha’ => Wrong

Relation Traversal

Relations between objects can be traversed using Java-like syntax. For example, if the Student class has a field named "addreess" of type Address, that relation can be queried as follows:

select x from Student x where x.address.aid = 1

select x.address.aid, x.address.city from Student x where x.address.aid = 1

 

Similar to relation traversal, nested embeddable objects can be traversed using Java-like syntax.

JPQL Example

Database script (MySQL)

CREATE TABLE ADDRESS(

AID INT(5) PRIMARY KEY AUTO_INCREMENT,

CITY VARCHAR(30),

ZIPCODE VARCHAR(30)

);                

 

CREATE TABLE STUDENT(

SID INT(5) PRIMARY KEY AUTO_INCREMENT,

SNAME VARCHAR(30),

AID INT(5),

CONSTRAINT FOREIGN KEY (AID) REFERENCES ADDRESS (AID)

);

 

INSERT  INTO ADDRESS(AID,CITY,ZIPCODE) VALUES (1,'Bangalore', '560010');

 

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (1,'Manu Manjunatha', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (2,'Advith Tyagraj', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (3,'Likitha', 1);

INSERT  INTO STUDENT(SID,SNAME,AID) VALUES (4,'Tyagraj', 1);

 

pom.xml

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/maven-v4_0_0.xsd">

        <modelVersion>4.0.0</modelVersion>

        <groupId>com.java4coding</groupId>

        <artifactId>JPQL_Example</artifactId>

        <packaging>jar</packaging>

        <version>1.0-SNAPSHOT</version>

        <name>JPQL_Example</name>

        <url>https://maven.apache.org</url>

        <dependencies>

                <dependency>

                        <groupId>junit</groupId>

                        <artifactId>junit</artifactId>

                        <version>3.8.1</version>

                        <scope>test</scope>

                </dependency>

                <dependency>

                        <groupId>org.eclipse.persistence</groupId>

                        <artifactId>javax.persistence</artifactId>

                        <version>2.0.0</version>

                </dependency>

 

                <dependency>

                        <groupId>org.hibernate</groupId>

                        <artifactId>hibernate-entitymanager</artifactId>

                        <version>4.2.8.Final</version>

                </dependency>

 

                <dependency>

                        <groupId>mysql</groupId>

                        <artifactId>mysql-connector-java</artifactId>

                        <version>8.0.11</version>

                </dependency>

        </dependencies>

</project>

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="https://java.sun.com/xml/ns/persistence"

        xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="https://java.sun.com/xml/ns/persistence

             https://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

        version="2.0">

 

        <persistence-unit name="StudentPU">

                <provider>org.hibernate.ejb.HibernatePersistence</provider>

                <properties>

                        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/study" />

                        <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver" />

                        <property name="hibernate.connection.username" value="root" />

                        <property name="hibernate.connection.password" value="root" />

                        <property name="hibernate.archive.autodetection" value="class" />

                        <property name="hibernate.show_sql" value="true" />

                        <property name="hibernate.format_sql" value="true" />

                        <property name="hbm2ddl.auto" value="update" />

                </properties>

        </persistence-unit>

</persistence>

Address.java

package com.java4coding;

 

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.Table;

 

@Entity

@Table(name = "ADDRESS")

public class Address {

       

        @Id

        private int aid;

       

        private String city;

 

        private String zipcode;

 

        public int getAid() {

                return aid;

        }

        public void setAid(int aid) {

                this.aid = aid;

        }

        public String getCity() {

                return city;

        }

        public void setCity(String city) {

                this.city = city;

        }

        public String getZipcode() {

                return zipcode;

        }

        public void setZipcode(String zipcode) {

                this.zipcode = zipcode;

        }

}

Student.java

package com.java4coding;

 

import javax.persistence.CascadeType;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.JoinColumn;

import javax.persistence.ManyToOne;

import javax.persistence.Table;

 

@Entity

@Table(name = "STUDENT")

public class Student {

       

        @Id

        @GeneratedValue(strategy = GenerationType.AUTO)

        private int sid;

       

        private String sname;

       

        @ManyToOne(cascade = CascadeType.ALL)

        @JoinColumn(name= "AID")

        private Address address;

 

        public int getSid() {

                return sid;

        }

        public void setSid(int sid) {

                this.sid = sid;

        }

        public String getSname() {

                return sname;

        }

        public void setSname(String sname) {

                this.sname = sname;

        }

        public Address getAddress() {

                return address;

        }

        public void setAddress(Address address) {

                this.address = address;

        }

}

Test.java

package com.java4coding;

 

import java.util.List;

 

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import javax.persistence.Persistence;

import javax.persistence.Query;

 

public class Test {

        public static void main(String[] args) {

                EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU");

                EntityManager em = emf.createEntityManager();

 

                em.getTransaction().begin();

 

                Query q = em.createQuery("SELECT x FROM Student x");

                List<Student> results = (List<Student>) q.getResultList();

               

                for(Student s: results) {

                        System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" );

                        System.out.println("Student name: " + s.getSname());

                        System.out.println("Student ID: " + s.getSid());

                        System.out.println("Student City: " + s.getAddress().getCity());

                        System.out.println("Student Adress ID: " + s.getAddress().getAid());

                        System.out.println("Student Zipcode: " + s.getAddress().getZipcode());

                        System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" );

                }

                em.getTransaction().commit();

        }

}

Project directory structure

jpa-query-0
 

Output:

Hibernate:

    select

        student0_.sid as sid1_1_,

        student0_.AID as AID3_1_,

        student0_.sname as sname2_1_

    from

        STUDENT student0_

Hibernate:

    select

        address0_.aid as aid1_0_0_,

        address0_.city as city2_0_0_,

        address0_.zipcode as zipcode3_0_0_

    from

        ADDRESS address0_

    where

        address0_.aid=?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Manu Manjunatha

Student ID: 1

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Advith Tyagraj

Student ID: 2

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Likitha

Student ID: 3

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Student name: Tyagraj

Student ID: 4

Student City: Bangalore

Student Adress ID: 1

Student Zipcode: 560010

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

All Chapters
Author